using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;

namespace OgilvyOne.SqlServerDAL
{
    public class FuncRole : IFuncRole
    {
        // Fields
        private const string PARM_FUNCTION_UID = "@Function_UID";
        private const string PARM_ISDEL = "@IsDel";
        private const string PARM_ROLE_UID = "@Role_UID";
        private const string PARM_ROLE_UIDS = "@Role_UIDS";
        private const string PARM_SFR_UID = "@SFR_UID";
        private const string SQL_DELETE_FUNCTION_ROLE = "UPDATE Sys_Function_Role SET IsDel = '1' WHERE SFR_UID = @SFR_UID";
        private const string SQL_DELETE_FUNCTION_ROLE_RID = "UPDATE Sys_Function_Role SET IsDel = '1' WHERE Role_UID = @Role_UID";
        private const string SQL_INSERT_FUNCTION_ROLE = "INSERT INTO Sys_Function_Role (Function_UID, Role_UID, IsDel) VALUES(@Function_UID,@Role_UID,'0')";
        private const string SQL_SELECT_FUNCTION_ROLE = "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where SFR_UID=@SFR_UID and IsDel='0'";
        private const string SQL_SELECT_FUNCTION_ROLE_LIST = "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role where IsDel='0'";
        private const string SQL_SELECT_FUNCTION_ROLE_RID = "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where Role_UID=@Role_UID and IsDel='0'";
        private const string SQL_SELECT_FUNCTION_ROLE_RIDS = "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where Role_UID in ({0}) and IsDel='0'";
        private const string SQL_UPDATE_FUNCTION_ROLE = "UPDATE Sys_Function_Role SET Function_UID = @Function_UID, Role_UID = @Role_UID WHERE SFR_UID = @SFR_UID";

        // Methods
        public void Delete(int funcrole_id)
        {
            SqlParameter[] functionRoleDeleteParameters = GetFunctionRoleDeleteParameters();
            functionRoleDeleteParameters[0].Value = funcrole_id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE Sys_Function_Role SET IsDel = '1' WHERE SFR_UID = @SFR_UID", functionRoleDeleteParameters);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void DeleteByRoleID(int role_id)
        {
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Role_UID", SqlDbType.Int, 4, "ROLE_UID") };
            cmdParms[0].Value = role_id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE Sys_Function_Role SET IsDel = '1' WHERE Role_UID = @Role_UID", cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public string getFuncByRoleID(string srole_id)
        {
            string str = "0";
            string cmdText = string.Format("SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where Role_UID in ({0}) and IsDel='0'", srole_id);
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, null))
            {
                while (reader.Read())
                {
                    str = str + "," + reader.GetInt32(1).ToString();
                }
            }
            return str;
        }

        public IList getFuncListByRoleID(int role_id)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@Role_UID", SqlDbType.Int, 4, "ROLE_UID") };
            cmdParms[0].Value = role_id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where Role_UID=@Role_UID and IsDel='0'", cmdParms))
            {
                while (reader.Read())
                {
                    FuncRoleInfo info = new FuncRoleInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2));
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getFuncRoleList()
        {
            IList list = new ArrayList();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role where IsDel='0'", null))
            {
                while (reader.Read())
                {
                    FuncRoleInfo info = new FuncRoleInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2));
                    list.Add(info);
                }
            }
            return list;
        }

        public IList getFuncRoleList(int SFR_id)
        {
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@SFR_UID", SqlDbType.Int, 4, "SFR_UID") };
            cmdParms[0].Value = SFR_id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, "SELECT SFR_UID,Function_UID,Role_UID FROM Sys_Function_Role  where SFR_UID=@SFR_UID and IsDel='0'", cmdParms))
            {
                while (reader.Read())
                {
                    FuncRoleInfo info = new FuncRoleInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2));
                    list.Add(info);
                }
            }
            return list;
        }

        private static SqlParameter[] GetFunctionRoleDeleteParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("UPDATE Sys_Function_Role SET IsDel = '1' WHERE SFR_UID = @SFR_UID");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@SFR_UID", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("UPDATE Sys_Function_Role SET IsDel = '1' WHERE SFR_UID = @SFR_UID", cachedParameters);
            }
            return cachedParameters;
        }

        private static SqlParameter[] GetFunctionRoleInsertParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("INSERT INTO Sys_Function_Role (Function_UID, Role_UID, IsDel) VALUES(@Function_UID,@Role_UID,'0')");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@Function_UID", SqlDbType.Int, 4), new SqlParameter("@Role_UID", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("INSERT INTO Sys_Function_Role (Function_UID, Role_UID, IsDel) VALUES(@Function_UID,@Role_UID,'0')", cachedParameters);
            }
            return cachedParameters;
        }

        private static SqlParameter[] GetFunctionRoleModifyParameters()
        {
            SqlParameter[] cachedParameters = SQLHelper.GetCachedParameters("UPDATE Sys_Function_Role SET Function_UID = @Function_UID, Role_UID = @Role_UID WHERE SFR_UID = @SFR_UID");
            if (cachedParameters == null)
            {
                cachedParameters = new SqlParameter[] { new SqlParameter("@SFR_UID", SqlDbType.Int, 4), new SqlParameter("@Function_UID", SqlDbType.Int, 4), new SqlParameter("@Role_UID", SqlDbType.Int, 4) };
                SQLHelper.CacheParameters("UPDATE Sys_Function_Role SET Function_UID = @Function_UID, Role_UID = @Role_UID WHERE SFR_UID = @SFR_UID", cachedParameters);
            }
            return cachedParameters;
        }

        public void Insert(FuncRoleInfo acc)
        {
            SqlParameter[] functionRoleInsertParameters = GetFunctionRoleInsertParameters();
            this.SetFunctionRoleInsertParameters(functionRoleInsertParameters, acc);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "INSERT INTO Sys_Function_Role (Function_UID, Role_UID, IsDel) VALUES(@Function_UID,@Role_UID,'0')", functionRoleInsertParameters);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        private void SetFunctionRoleInsertParameters(SqlParameter[] parms, FuncRoleInfo acc)
        {
            parms[0].Value = acc.Function_UID;
            parms[1].Value = acc.Role_UID;
        }

        private void SetFunctionRoleModifyParameters(SqlParameter[] parms, FuncRoleInfo acc)
        {
            parms[0].Value = acc.SFR_UID;
            parms[1].Value = acc.Function_UID;
            parms[2].Value = acc.Role_UID;
        }

        public void Update(FuncRoleInfo myItem)
        {
            SqlParameter[] functionRoleModifyParameters = GetFunctionRoleModifyParameters();
            this.SetFunctionRoleModifyParameters(functionRoleModifyParameters, myItem);
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, "UPDATE Sys_Function_Role SET Function_UID = @Function_UID, Role_UID = @Role_UID WHERE SFR_UID = @SFR_UID", functionRoleModifyParameters);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
